package com.xiangyi.teachingsystem.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xiangyi.teachingsystem.entity.po.Course;
import com.xiangyi.teachingsystem.entity.po.CourseUser;
import com.xiangyi.teachingsystem.entity.po.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface CourseUserMapper extends BaseMapper<CourseUser> {

    @Select("SELECT cu.id,cu.sign," +
            "u.id user_id, u.name, u.college, u.grade, u.class_name, u.job_num, u.sex, u.head_url" +
            ",c.course_name\n" +
            "FROM course_user cu\n" +
            "LEFT JOIN `user` u\n" +
            "ON cu.user_id=u.id\n" +
            "LEFT JOIN course c\n" +
            "ON cu.sign=c.sign\n" +
            "WHERE c.id=#{courseId}")
    List<CourseUser> usersList(Integer courseId);

    @Select("SELECT c.id,c.course_desc,c.course_name,c.course_url,c.sign,\n" +
            "\t\t(SELECT u.`name`\n" +
            "\t\tFROM user_course uc\n" +
            "\t\tLEFT JOIN `user` u\n" +
            "\t\tON uc.user_id=u.id\n" +
            "\t\tWHERE uc.course_id=c.id) `name`\n" +
            "FROM course_user cu\n" +
            "LEFT JOIN course c\n" +
            "ON cu.sign=c.sign\n" +
            "WHERE cu.user_id=#{userId}")
    List<Course> joinCourseList(Integer userId);

    @Select("SELECT u.id\n" +
            "FROM user_course uc\n" +
            "LEFT JOIN `user` u\n" +
            "ON uc.user_id=u.id\n" +
            "WHERE uc.course_id=(SELECT c.id\n" +
            "FROM course c\n" +
            "WHERE c.sign=#{sign})")
    User getCourseUserBySign(String sign);
}
